"""
Case Type   : Ubtree
Case Name   : alter ubtree索引(指定PCR)_索引不可用后重建表索引
Create At   : 2025-11-04
Owner       : xiongqin3
Description :
    1:建表;
    2:建索引时指定PCR
    3:插入数据
    4:设置表索引不可用
    5:重建表索引
    6:查询
    7:查询计划
    8:清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、使用indexscan
    8、成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class UstoreUbtreeCase0062(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0062'
        self.prepare_data_01 = f"drop table if exists {self.table_name} cascade;" \
                               f"create table {self.table_name} (log_id bigserial primary key, log_time timestamp not null, content text, severity varchar(10)) with (storage_type=ustore);"    
        self.prepare_data_02 = f"create index i_ubtree_0062 on {self.table_name} using ubtree(log_time) with (index_type='pcr');" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0062%';"
        self.prepare_data_03 = f"insert into {self.table_name} (log_time, content, severity)" \
                               f"select timestamp '2024-01-01 00:00:00' + (generate_series(1,100000) || ' seconds')::interval," \
                               f"'Log entry ' || generate_series(1,100000), case when random() < 0.1 then 'ERROR'" \
                               f"when random() < 0.3 then 'WARNING' else 'INFO' end;"
        self.prepare_data_04 = f"alter index if exists i_ubtree_0062 unusable;" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0062%';" 
        self.prepare_data_05 = f"alter index i_ubtree_0062 rebuild;" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0062%';"
        self.prepare_data_06 = f"select count(*) from {self.table_name} where log_time between '2024-01-01 12:00:00' and '2024-01-01 13:00:00';"
        self.prepare_data_07 = f"set enable_seqscan = off;" \
                               f"explain (costs off) select * from {self.table_name} where log_time between '2024-01-01 12:00:00' and '2024-01-01 13:00:00';"
        self.prepare_data_08 = f"drop table if exists {self.table_name} cascade;" \
                               f"set enable_seqscan = on;"

    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step4:设置表索引不可用;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.ALTER_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)

        text = '--step5:重建表索引;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)

        text = '--step6:查询;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn(" 3601\n", data_cmd, '执行失败' + text)

        text = '--step7:查询计划;expect:使用indexscan--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertIn("Bitmap Index Scan on i_ubtree_0062", data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
